package com.vf.core.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.LinkedList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {

	public static void main(String[] args) throws Exception {
		Workbook workbook=readExcel("d:/Tes1s.xls");
		List<Sheet> sheets= ExcelUtil.getSheet( workbook);
		if(sheets!=null && sheets.size()>0) {
			for(Sheet sheet:sheets) {
				List<Row> rows=ExcelUtil.getRows(sheet);
				for(Row row:rows) {
					List<Cell> cells=ExcelUtil.getCells(row);
					System.out.println(cells.get(0).getStringCellValue());
				}
			}
		}
		
	}


	
	public static Workbook readExcel(String excelPath) throws Exception {
		// 创建excel工作簿对象
		Workbook workbook = null;
		//FormulaEvaluator formulaEvaluator = null;
		// 读取目标文件
		File excelFile = new File(excelPath);
		
		InputStream is = null ;
		try {
			is = new FileInputStream(excelFile);
			// 判断文件是xlsx还是xls
			if (excelFile.getName().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(is);
			} else {
				workbook = new HSSFWorkbook(is);
			}
		}catch (Exception e) {
			 throw e; //
		}finally {
			try {
				is.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		
		return workbook;
	}
	
	/**
	 * 获取文件中的所有sheet
	 * @param workbook
	 * @return 返回有内容的Sheet
	 */
	public static List<Sheet> getSheet(Workbook workbook) {
		List<Sheet> sheets=new LinkedList<Sheet>();
		if(workbook!=null) {
			for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
				Sheet sheet = workbook.getSheetAt(numSheet);
				if (sheet == null) {
					continue;
				}
				sheets.add(sheet);
			}
		}
		return sheets;
	}
	
	/**
	 * 获取 sheet 中的rows
	 * @param sheet
	 * @return
	 */
	public static List<Row> getRows(Sheet sheet) {
		List<Row> rows=new LinkedList<Row>();
		if(sheet!=null) {
			for (int rownum = 0; rownum < sheet.getLastRowNum(); rownum++) {
				Row row = sheet.getRow(rownum);
				rows.add(row);
			}
		}
		return rows;
	}

	/**
	 * 获取行的cells
	 * @param row
	 * @return
	 */
	public static List<Cell> getCells(Row row) {
		List<Cell> cells=new LinkedList<Cell>();
		if(row!=null) {
			for (int cellnum = 0; cellnum < row.getLastCellNum(); cellnum++) {
				Cell cell = row.getCell(cellnum);
				cells.add(cell);
			}
		}
		return cells;
	}
	
}
